/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BL;

import Bean.Khoa;
import Util.DBConnection;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author AD_DA
 */
public class BLKhoa {
    public List<Khoa> getDSKhoa(int MaTruong) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        List<Khoa> listKhoa=new ArrayList<Khoa>();
        if(conn!=null)
        {
            Statement stm=(Statement) conn.createStatement();
            String sql="SELECT MAKHOA, MATRUONG, TENKHOA, GIOITHIEUKHOA, LOGOKHOA FROM khoa WHERE MATRUONG="+MaTruong;
            ResultSet rs=stm.executeQuery(sql);
            while(rs.next()){
                Khoa kh=new Khoa();
                kh.setMaKhoa(rs.getInt("MAKHOA"));
                kh.setMaTruong(rs.getInt("MATRUONG"));
                kh.setTenKhoa(rs.getString("TENKHOA"));
                kh.setGioiThieu(rs.getString("GIOITHIEUKHOA"));
                kh.setLogo(rs.getString("LOGOKHOA"));
                listKhoa.add(kh);
            }
        }
        conn.close();
        return listKhoa;
    }
    public Khoa getKhoa(int MaKhoa) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        Khoa kh=new Khoa();
        if(conn!=null)
        {
            Statement stm=(Statement) conn.createStatement();
            String sql="SELECT MAKHOA, MATRUONG, TENKHOA, GIOITHIEUKHOA, LOGOKHOA FROM khoa WHERE MAKHOA="+MaKhoa;
            ResultSet rs=stm.executeQuery(sql);
            if(rs.next()){                
                kh.setMaKhoa(rs.getInt("MAKHOA"));
                kh.setMaTruong(rs.getInt("MATRUONG"));
                kh.setTenKhoa(rs.getString("TENKHOA"));
                kh.setGioiThieu(rs.getString("GIOITHIEUKHOA"));
                kh.setLogo(rs.getString("LOGOKHOA"));
            }
        }
        conn.close();
        return kh;
    }
    public int InsertKhoa(int MAKHOA, int MATRUONG, String TENKHOA, String GIOITHIEUKHOA, String LOGOKHOA) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        int rs=0;
        String sql="INSERT INTO khoa(MAKHOA, MATRUONG, TENKHOA, GIOITHIEUKHOA, LOGOKHOA) VALUES (?,?,?,?,?)";
        if(conn!=null){
            PreparedStatement preStm=(PreparedStatement) conn.prepareStatement(sql);
            preStm.setInt(1,MAKHOA );
            preStm.setInt(2,MATRUONG);
            preStm.setString(3,TENKHOA );
            preStm.setString(4,GIOITHIEUKHOA );
            preStm.setString(5,LOGOKHOA );
            rs=preStm.executeUpdate(sql);
        }
        conn.close();
        return rs;
    }
    public int UpdateKhoa(int MAKHOA, int MATRUONG, String TENKHOA, String GIOITHIEUKHOA, String LOGOKHOA) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        int rs=0;
        String sql="UPDATE khoa SET MATRUONG=?,TENKHOA=?,GIOITHIEUKHOA=?,LOGOKHOA=? WHERE MAKHOA=?";
        if(conn!=null){
            PreparedStatement preStm=(PreparedStatement) conn.prepareStatement(sql);
            preStm.setInt(1,MATRUONG);
            preStm.setString(2,TENKHOA );
            preStm.setString(3,GIOITHIEUKHOA );
            preStm.setString(4,LOGOKHOA );
            preStm.setInt(5,MAKHOA );
            rs=preStm.executeUpdate(sql);
        }
        conn.close();
        return rs;
    }
    public int DeleteKhoa(int MAKHOA) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        int rs=0;
        String sql="DELETE FROM khoa WHERE MAKHOA="+MAKHOA;
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            rs=stm.executeUpdate(sql);
        }
        conn.close();
        return rs;
    }
    public List<String> getDSMaNganh(int MaKhoa) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        List<String> listMaNganh=new ArrayList<String>();
        String sql="SELECT MANGANH FROM nganh WHERE MAKHOA="+MaKhoa;
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            while(rs.next()){
                String Ma=rs.getString("MANGANH");
                listMaNganh.add(Ma);
            }           
        }
        conn.close();
        return listMaNganh;
    }
    public List<String> getDSTenNganh(int MaKhoa) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        List<String> listTenNganh=new ArrayList<String>();
        String sql="SELECT TENNGANH FROM nganh WHERE MAKHOA="+MaKhoa;
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            while(rs.next()){
                String Ten=rs.getString("TENNGANH");
                listTenNganh.add(Ten);
            }           
        }
        conn.close();
        return listTenNganh;
    }
}
